package com.hx.springboot1.dao;

import com.hx.springboot1.pojo.dto.ScoreDTO;
import com.hx.springboot1.pojo.dto.ScoreReportDTO;
import com.hx.springboot1.pojo.entity.TabChoose;
import com.hx.springboot1.pojo.entity.TabScore;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Author: hx
 * Date: 2021-05-14 15/53
 * Description: Nothing else
 */
@Repository
@Mapper
public interface ScoreDao {
    @Delete("delete from tab_score where score_id = #{sId}")
    int delScoreByStuId(int sId);
    @Select("select score_id,a.stu_id,stu_name,a.course_id,course_name,score_time,score_score from tab_score a\n" +
            " join tab_student b on a.stu_id=b.stu_id\n" +
            " join tab_course c on a.course_id=c.course_id" +
            " order by a.stu_id")
    List<ScoreDTO> selectAllScore();
    @Select("select score_id,a.stu_id,stu_name,a.course_id,course_name,score_time,score_score from tab_score a\n" +
            " join tab_student b on a.stu_id=b.stu_id\n" +
            " join tab_course c on a.course_id=c.course_id" +
            " where a.stu_id = #{stuId}" +
            " order by a.stu_id")
    List<ScoreDTO> selectAllScoreByStuId(int stuId);
    @Select("select score_id,a.stu_id,stu_name,a.course_id,course_name,score_time,score_score from tab_score a\n" +
            " join tab_student b on a.stu_id=b.stu_id\n" +
            " join tab_course c on a.course_id=c.course_id" +
            " where stu_name like #{searchName}" +
            " order by a.stu_id")
    List<ScoreDTO> selectAllScoreByStuName(String searchName);
    @Select("select * from tab_score where score_id = #{scoreId}")
    TabScore selectScoreByScoreId(int scoreId);
    @Insert("insert into tab_score(stu_id,course_id,score_time,score_score)" +
            " values(#{stuId},#{courseId},#{scoreTime},#{scoreScore})")
    int insertScore(int stuId,int courseId,int scoreTime,int scoreScore);
    @Update("update tab_score set score_time = #{scoreTime},score_score = #{scoreScore} where score_id = #{scoreId}")
    int updateScore(int scoreTime,int scoreScore,int scoreId);
    @Select("select a.stu_id,stu_name,GROUP_CONCAT(DISTINCT(course_name)) as course_name,\n" +
            "AVG(score_score) as avg,MAX(score_score) as max,MIN(score_score) as min\n" +
            "from tab_score a\n" +
            "join tab_student b on a.stu_id = b.stu_id\n" +
            "join tab_choose c on b.stu_id = c.stu_id\n" +
            "join tab_course d on c.course_id = d.course_id\n" +
            "where c.year_id = #{yearId}\n" +
            "GROUP BY a.stu_id")
    List<ScoreReportDTO> selectReportByYearId(int yearId);
    @Select("select a.stu_id,stu_name,GROUP_CONCAT(DISTINCT(course_name)) as course_name,\n" +
            "AVG(score_score) as avg,MAX(score_score) as max,MIN(score_score) as min\n" +
            "from tab_score a\n" +
            "join tab_student b on a.stu_id = b.stu_id\n" +
            "join tab_choose c on b.stu_id = c.stu_id\n" +
            "join tab_course d on c.course_id = d.course_id\n" +
            "GROUP BY a.stu_id")
    List<ScoreReportDTO> selectAllReport();
    @Select("select * from tab_score where stu_id = #{stuId} and course_id = #{courseId}")
    TabScore selectByStuIdAndCourseId(int stuId,int courseId);
}
